package com.ruoyi.common.utils.poi

import com.ruoyi.common.core.text.Convert
import com.ruoyi.common.core.text.Convert.toBigDecimal
import com.ruoyi.common.core.text.Convert.toDouble
import com.ruoyi.common.core.text.Convert.toFloat
import com.ruoyi.common.core.text.Convert.toInt
import com.ruoyi.common.core.text.Convert.toLong
import com.ruoyi.common.core.text.Convert.toStr
import com.ruoyi.common.exception.UtilException
import com.ruoyi.common.utils.*
import com.ruoyi.common.utils.StringUtils.isNotEmpty
import com.ruoyi.common.utils.file.FileTypeUtils
import com.ruoyi.common.utils.file.FileUtils
import com.ruoyi.common.utils.file.ImageUtils
import com.ruoyi.common.utils.reflect.ReflectUtils
import com.ruoyi.framework.aspectj.lang.annotation.Excel
import com.ruoyi.framework.aspectj.lang.annotation.Excel.ColumnType
import com.ruoyi.framework.aspectj.lang.annotation.Excels
import com.ruoyi.framework.config.RuoYiConfig
import com.ruoyi.framework.web.domain.AjaxResult
import org.apache.commons.lang3.ArrayUtils
import org.apache.commons.lang3.RegExUtils
import org.apache.commons.lang3.StringUtils
import org.apache.commons.lang3.reflect.FieldUtils
import org.apache.poi.hssf.usermodel.HSSFClientAnchor
import org.apache.poi.hssf.usermodel.HSSFPicture
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.*
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.ss.util.CellRangeAddressList
import org.apache.poi.util.IOUtils
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.*
import org.slf4j.LoggerFactory
import java.io.*
import java.lang.reflect.Field
import java.lang.reflect.Method
import java.lang.reflect.ParameterizedType
import java.math.BigDecimal
import java.text.DecimalFormat
import java.time.LocalDate
import java.time.LocalDateTime
import java.util.*
import java.util.function.Function
import java.util.stream.Collectors
import javax.servlet.http.HttpServletResponse

/**
 * Excel相关处理
 *
 * @author ruoyi
 */
class ExcelUtil<T>(
    /**
     * 实体对象
     */
    var clazz: Class<T>,
) {
    /**
     * 工作表名称
     */
    private var sheetName: String? = null

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private var type: Excel.Type? = null

    /**
     * 工作薄对象
     */
    private var wb: Workbook? = null

    /**
     * 工作表对象
     */
    private var sheet: Sheet? = null

    /**
     * 样式列表
     */
    private var styles: Map<String?, CellStyle>? = null

    /**
     * 导入导出数据列表
     */
    private var list: List<T>? = null

    /**
     * 注解列表
     */
    private var fields: List<Array<Any>>? = null

    /**
     * 当前行号
     */
    private var rownum = 0

    /**
     * 标题
     */
    private var title: String? = null

    /**
     * 最大高度
     */
    private var maxHeight: Short = 0

    /**
     * 合并后最后行数
     */
    private var subMergedLastRowNum = 0

    /**
     * 合并后开始行数
     */
    private var subMergedFirstRowNum = 1

    /**
     * 对象的子列表方法
     */
    private var subMethod: Method? = null

    /**
     * 对象的子列表属性
     */
    private var subFields: List<Field>? = null

    /**
     * 统计列表
     */
    private val statistics: MutableMap<Int, Double> = HashMap()

    /**
     * 需要排除列属性
     */
    var excludeFields: List<String> = ArrayList()

    /**
     * 隐藏Excel中列属性
     *
     * @param fields 列属性名 示例[单个"name"/多个"id","name"]
     * @throws Exception
     */
    fun hideColumn(vararg fields: String) {
        excludeFields = fields.toMutableList()
    }

    fun init(list: List<T>?, sheetName: String?, title: String?, type: Excel.Type?) {
        this.list = list
        this.sheetName = sheetName
        this.type = type
        this.title = title
        createExcelField()
        createWorkbook()
        createTitle()
        createSubHead()
    }

    /**
     * 创建excel第一行标题
     */
    fun createTitle() {
        if (isNotEmpty(title)) {
            subMergedFirstRowNum++
            subMergedLastRowNum++
            var titleLastCol = fields!!.size - 1
            if (isSubList) {
                titleLastCol = titleLastCol + subFields!!.size - 1
            }
            val titleRow = sheet!!.createRow(if (rownum == 0) rownum++ else 0)
            titleRow.heightInPoints = 30f
            val titleCell = titleRow.createCell(0)
            titleCell.cellStyle = styles!!["title"]
            titleCell.setCellValue(title)
            sheet!!.addMergedRegion(CellRangeAddress(titleRow.rowNum, titleRow.rowNum, titleRow.rowNum, titleLastCol))
        }
    }

    /**
     * 创建对象的子列表名称
     */
    fun createSubHead() {
        if (isSubList) {
            subMergedFirstRowNum++
            subMergedLastRowNum++
            val subRow = sheet!!.createRow(rownum)
            var excelNum = 0
            fields!!.forEach { objects ->
                val attr = objects[1] as Excel
                val headCell1 = subRow.createCell(excelNum)
                headCell1.setCellValue(attr.name)
                headCell1.cellStyle = styles!![com.ruoyi.common.utils.StringUtils.format(
                    "header_{}_{}",
                    attr.headerColor,
                    attr.headerBackgroundColor
                )]
                excelNum++
            }
            val headFirstRow = excelNum - 1
            val headLastRow = headFirstRow + subFields!!.size - 1
            if (headLastRow > headFirstRow) {
                sheet!!.addMergedRegion(CellRangeAddress(rownum, rownum, headFirstRow, headLastRow))
            }
            rownum++
        }
    }
    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @param titleNum 标题占用行数
     * @return 转换后集合
     */
    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @return 转换后集合
     */
    @JvmOverloads
    @Throws(Exception::class)
    fun importExcel(`is`: InputStream?, titleNum: Int = 0): List<T?> {
        return importExcel(StringUtils.EMPTY, `is`, titleNum)
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetName 表格索引名
     * @param titleNum 标题占用行数
     * @param is 输入流
     * @return 转换后集合
     */
    @Throws(Exception::class)
    fun importExcel(sheetName: String?, `is`: InputStream?, titleNum: Int): List<T?> {
        type = Excel.Type.IMPORT
        wb = WorkbookFactory.create(`is`)
        val list: MutableList<T?> = ArrayList()
        // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
        // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
        val sheet: Sheet? = if (isNotEmpty(sheetName)) {
            wb!!.getSheet(sheetName)
        } else {
            wb!!.getSheetAt(0)
        }
        if (sheet == null) {
            throw IOException("文件sheet不存在")
        }
        val isXSSFWorkbook = wb !is HSSFWorkbook
        val pictures: Map<String, PictureData> = if (isXSSFWorkbook) {
            getSheetPictures07(sheet as XSSFSheet, wb as XSSFWorkbook?)
        } else {
            getSheetPictures03(sheet as HSSFSheet, wb as HSSFWorkbook?)
        }
        // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
        val rows = sheet.lastRowNum
        if (rows > 0) {
            // 定义一个map用于存放excel列的序号和field.
            val cellMap: MutableMap<String?, Int> = HashMap()
            // 获取表头
            val heard = sheet.getRow(titleNum)
            for (i in 0 until heard.physicalNumberOfCells) {
                val cell = heard.getCell(i)
                if (com.ruoyi.common.utils.StringUtils.isNotNull(cell)) {
                    val value = getCellValue(heard, i).toString()
                    cellMap[value] = i
                } else {
                    cellMap[null] = i
                }
            }
            // 有数据时才处理 得到类的所有field.
            val fields = getFields()
            val fieldsMap: MutableMap<Int, Array<Any>> = HashMap()
            for (objects in fields) {
                val attr = objects[1] as Excel
                val column = cellMap[attr.name]
                if (column != null) {
                    fieldsMap[column] = objects
                }
            }
            for (i in titleNum + 1..rows) {
                // 从第2行开始取数据,默认第一行是表头.
                val row = sheet.getRow(i)
                // 判断当前行是否是空行
                if (isRowEmpty(row)) {
                    continue
                }
                var entity: T? = null
                for ((key, value) in fieldsMap) {
                    var `val` = getCellValue(row, key)

                    // 如果不存在实例则新建.
                    entity = entity ?: clazz.getDeclaredConstructor().newInstance()
                    // 从map中得到对应列的field.
                    val field = value[0] as Field
                    val attr = value[1] as Excel
                    // 取得类型,并根据对象类型设置值.
                    val fieldType = field.type
                    if (String::class.java == fieldType) {
                        val s: String? = toStr(`val`)
                        `val` = if (StringUtils.endsWith(s, ".0")) {
                            StringUtils.substringBefore(s, ".0")
                        } else {
                            val dateFormat: String = field.getAnnotation(Excel::class.java).dateFormat
                            if (isNotEmpty(dateFormat)) {
                                parseDateToStr(dateFormat, `val`)
                            } else {
                                toStr(`val`)
                            }
                        }
                    } else if ((Integer.TYPE == fieldType || Int::class.java == fieldType) && StringUtils.isNumeric(
                            toStr(`val`)
                        )
                    ) {
                        `val` = toInt(`val`)
                    } else if ((java.lang.Long.TYPE == fieldType || Long::class.java == fieldType) && StringUtils.isNumeric(
                            toStr(`val`)
                        )
                    ) {
                        `val` = toLong(`val`)
                    } else if (java.lang.Double.TYPE == fieldType || Double::class.java == fieldType) {
                        `val` = toDouble(`val`)
                    } else if (java.lang.Float.TYPE == fieldType || Float::class.java == fieldType) {
                        `val` = toFloat(`val`)
                    } else if (BigDecimal::class.java == fieldType) {
                        `val` = toBigDecimal(`val`)
                    } else if (Date::class.java == fieldType) {
                        if (`val` is String) {
                            `val` = DateUtils.parseDate(`val`)
                        } else if (`val` is Double) {
                            `val` = DateUtil.getJavaDate((`val` as Double?)!!)
                        }
                    } else if (java.lang.Boolean.TYPE == fieldType || Boolean::class.java == fieldType) {
                        `val` = Convert.toBool(`val`, false)
                    }
                    if (com.ruoyi.common.utils.StringUtils.isNotNull(fieldType)) {
                        var propertyName = field.name
                        if (isNotEmpty(attr.targetAttr)) {
                            propertyName = field.name + "." + attr.targetAttr
                        } else if (StringUtils.isNotEmpty(attr.readConverterExp)) {
                            `val` = toStr(`val`)?.let { reverseByExp(it, attr.readConverterExp, attr.separator) }
                        } else if (isNotEmpty(attr.dictType)) {
                            `val` = toStr(`val`)?.let { reverseDictByExp(it, attr.dictType, attr.separator) }
                        } else if (attr.handler != ExcelHandlerAdapter::class.java) {
                            `val` = dataFormatHandlerAdapter(`val`, attr)
                        } else if (ColumnType.IMAGE == attr.cellType && isNotEmpty(
                                pictures
                            )
                        ) {
                            val image = pictures[row.rowNum.toString() + "_" + key]
                            `val` = if (image == null) {
                                ""
                            } else {
                                val data = image.data
                                FileUtils.writeImportBytes(data)
                            }
                        }
                        ReflectUtils.invokeSetter(entity, propertyName, `val`)
                    }
                }
                list.add(entity)
            }
        }
        return list
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    @JvmOverloads
    fun exportExcel(list: List<T>?, sheetName: String?, title: String? = StringUtils.EMPTY): AjaxResult {
        init(list, sheetName, title, Excel.Type.EXPORT)
        return exportExcel()
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response 返回数据
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    @JvmOverloads
    fun exportExcel(
        response: HttpServletResponse,
        list: List<T>?,
        sheetName: String?,
        title: String? = StringUtils.EMPTY,
    ) {
        response.contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        response.characterEncoding = "utf-8"
        init(list, sheetName, title, Excel.Type.EXPORT)
        exportExcel(response)
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     * @return 结果
     */
    @JvmOverloads
    fun importTemplateExcel(sheetName: String?, title: String? = StringUtils.EMPTY): AjaxResult {
        init(null, sheetName, title, Excel.Type.IMPORT)
        return exportExcel()
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     * @return 结果
     */
    @JvmOverloads
    fun importTemplateExcel(response: HttpServletResponse, sheetName: String?, title: String? = StringUtils.EMPTY) {
        response.contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        response.characterEncoding = "utf-8"
        init(null, sheetName, title, Excel.Type.IMPORT)
        exportExcel(response)
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    fun exportExcel(response: HttpServletResponse) {
        try {
            writeSheet()
            wb!!.write(response.outputStream)
        } catch (e: Exception) {
            log.error("导出Excel异常{}", e.message)
        } finally {
            IOUtils.closeQuietly(wb)
        }
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    fun exportExcel(): AjaxResult {
        var out: OutputStream? = null
        return try {
            writeSheet()
            val filename = encodingFilename(sheetName)
            out = FileOutputStream(getAbsoluteFile(filename))
            wb!!.write(out)
            AjaxResult.Companion.success(filename)
        } catch (e: Exception) {
            log.error("导出Excel异常{}", e.message)
            throw UtilException("导出Excel失败，请联系网站管理员！")
        } finally {
            IOUtils.closeQuietly(wb)
            IOUtils.closeQuietly(out)
        }
    }

    /**
     * 创建写入数据到Sheet
     */
    fun writeSheet() {
        // 取出一共有多少个sheet.
        val sheetNo = Math.max(1, Math.ceil(list!!.size * 1.0 / sheetSize).toInt())
        for (index in 0 until sheetNo) {
            createSheet(sheetNo, index)

            // 产生一行
            val row = sheet!!.createRow(rownum)
            var column = 0
            // 写入各个字段的列头名称
            for (os in fields!!) {
                val field = os[0] as Field
                val excel = os[1] as Excel
                if (MutableCollection::class.java.isAssignableFrom(field.type)) {
                    for (subField in subFields!!) {
                        val subExcel = subField.getAnnotation(Excel::class.java)
                        createHeadCell(subExcel, row, column++)
                    }
                } else {
                    createHeadCell(excel, row, column++)
                }
            }
            if (Excel.Type.EXPORT == type) {
                fillExcelData(index)
                addStatisticsRow()
            }
        }
    }

    /**
     * 填充excel数据
     *
     * @param index 序号
     */
    fun fillExcelData(index: Int) {
        val startNo = index * sheetSize
        val endNo = (startNo + sheetSize).coerceAtMost(list!!.size)
        var rowNo = 1 + rownum - startNo
        (startNo until endNo).forEach { i ->
            rowNo = if (i > 1) rowNo + 1 else rowNo + i
            var row = sheet!!.createRow(rowNo)
            // 得到导出对象.
            val vo = list!![i]
            var subList: Collection<*>? = null
            if (isSubListValue(vo)) {
                subList = getListCellValue(vo)
                subMergedLastRowNum += subList.size
            }
            var column = 0
            fields!!.forEach { os ->
                val field = os[0] as Field
                val excel = os[1] as Excel
                if (MutableCollection::class.java.isAssignableFrom(field.type) && com.ruoyi.common.utils.StringUtils.isNotNull(
                        subList
                    )
                ) {
                    var subFirst = false
                    subList!!.forEach { obj ->
                        if (subFirst) {
                            rowNo++
                            row = sheet!!.createRow(rowNo)
                        }
                        val subFields = FieldUtils.getFieldsListWithAnnotation(obj!!.javaClass, Excel::class.java)
                        subFields.withIndex().forEach { (subIndex, subField) ->
                            if (subField.isAnnotationPresent(Excel::class.java)) {
                                subField.isAccessible = true
                                val attr = subField.getAnnotation(Excel::class.java)
                                addCell(attr, row, obj as T, subField, column + subIndex)
                            }
                        }
                        subFirst = true
                    }
                    subMergedFirstRowNum += subList.size
                } else {
                    addCell(excel, row, vo, field, column++)
                }
            }
        }
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private fun createStyles(wb: Workbook): Map<String?, CellStyle> {
        // 写入各条记录,每条记录对应excel表中的一行
        val styles: MutableMap<String?, CellStyle> = HashMap()
        var style = wb.createCellStyle()
        style.alignment = HorizontalAlignment.CENTER
        style.verticalAlignment = VerticalAlignment.CENTER
        val titleFont = wb.createFont()
        titleFont.fontName = "Arial"
        titleFont.fontHeightInPoints = 16.toShort()
        titleFont.bold = true
        style.setFont(titleFont)
        styles["title"] = style
        style = wb.createCellStyle()
        style.alignment = HorizontalAlignment.CENTER
        style.verticalAlignment = VerticalAlignment.CENTER
        style.borderRight = BorderStyle.THIN
        style.rightBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
        style.borderLeft = BorderStyle.THIN
        style.leftBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
        style.borderTop = BorderStyle.THIN
        style.topBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
        style.borderBottom = BorderStyle.THIN
        style.bottomBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
        val dataFont = wb.createFont()
        dataFont.fontName = "Arial"
        dataFont.fontHeightInPoints = 10.toShort()
        style.setFont(dataFont)
        styles["data"] = style
        style = wb.createCellStyle()
        style.alignment = HorizontalAlignment.CENTER
        style.verticalAlignment = VerticalAlignment.CENTER
        val totalFont = wb.createFont()
        totalFont.fontName = "Arial"
        totalFont.fontHeightInPoints = 10.toShort()
        style.setFont(totalFont)
        styles["total"] = style
        styles.putAll(annotationHeaderStyles(wb, styles))
        styles.putAll(annotationDataStyles(wb))
        return styles
    }

    /**
     * 根据Excel注解创建表格头样式
     *
     * @param wb 工作薄对象
     * @return 自定义样式列表
     */
    private fun annotationHeaderStyles(wb: Workbook, styles: Map<String?, CellStyle>): Map<String?, CellStyle> {
        val headerStyles: MutableMap<String?, CellStyle> = HashMap()
        for (os in fields!!) {
            val excel = os[1] as Excel
            val key = com.ruoyi.common.utils.StringUtils.format(
                "header_{}_{}",
                excel.headerColor,
                excel.headerBackgroundColor
            )
            if (!headerStyles.containsKey(key)) {
                var style = wb.createCellStyle()
                style = wb.createCellStyle()
                style.cloneStyleFrom(styles["data"])
                style.alignment = HorizontalAlignment.CENTER
                style.verticalAlignment = VerticalAlignment.CENTER
                style.fillForegroundColor = excel.headerBackgroundColor.index
                style.fillPattern = FillPatternType.SOLID_FOREGROUND
                val headerFont = wb.createFont()
                headerFont.fontName = "Arial"
                headerFont.fontHeightInPoints = 10.toShort()
                headerFont.bold = true
                headerFont.color = excel.headerColor.index
                style.setFont(headerFont)
                headerStyles[key] = style
            }
        }
        return headerStyles
    }

    /**
     * 根据Excel注解创建表格列样式
     *
     * @param wb 工作薄对象
     * @return 自定义样式列表
     */
    private fun annotationDataStyles(wb: Workbook): Map<String?, CellStyle> {
        val styles: MutableMap<String?, CellStyle> = HashMap()
        for (os in fields!!) {
            val excel = os[1] as Excel
            val key = com.ruoyi.common.utils.StringUtils.format(
                "data_{}_{}_{}",
                excel.align,
                excel.color,
                excel.backgroundColor
            )
            if (!styles.containsKey(key)) {
                var style = wb.createCellStyle()
                style = wb.createCellStyle()
                style.alignment = excel.align
                style.verticalAlignment = VerticalAlignment.CENTER
                style.borderRight = BorderStyle.THIN
                style.rightBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
                style.borderLeft = BorderStyle.THIN
                style.leftBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
                style.borderTop = BorderStyle.THIN
                style.topBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
                style.borderBottom = BorderStyle.THIN
                style.bottomBorderColor = IndexedColors.GREY_50_PERCENT.getIndex()
                style.fillPattern = FillPatternType.SOLID_FOREGROUND
                style.fillForegroundColor = excel.backgroundColor.getIndex()
                val dataFont = wb.createFont()
                dataFont.fontName = "Arial"
                dataFont.fontHeightInPoints = 10.toShort()
                dataFont.color = excel.color.index
                style.setFont(dataFont)
                styles[key] = style
            }
        }
        return styles
    }

    /**
     * 创建单元格
     */
    fun createHeadCell(attr: Excel, row: Row, column: Int): Cell {
        // 创建列
        val cell = row.createCell(column)
        // 写入列信息
        cell.setCellValue(attr.name)
        setDataValidation(attr, row, column)
        cell.cellStyle = styles!![com.ruoyi.common.utils.StringUtils.format(
            "header_{}_{}",
            attr.headerColor,
            attr.headerBackgroundColor
        )]
        if (isSubList) {
            // 填充默认样式，防止合并单元格样式失效
            sheet!!.setDefaultColumnStyle(
                column,
                styles!![com.ruoyi.common.utils.StringUtils.format(
                    "data_{}_{}_{}",
                    attr.align,
                    attr.color,
                    attr.backgroundColor
                )]
            )
            if (attr.needMerge) {
                sheet!!.addMergedRegion(CellRangeAddress(rownum - 1, rownum, column, column))
            }
        }
        return cell
    }

    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr 注解相关
     * @param cell 单元格信息
     */
    fun setCellVo(value: Any, attr: Excel, cell: Cell) {
        if (ColumnType.STRING == attr.cellType) {
            var cellValue: String? = toStr(value)
            // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
            if (StringUtils.startsWithAny(cellValue, *FORMULA_STR)) {
                cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0")
            }
            cell.setCellValue(if (com.ruoyi.common.utils.StringUtils.isNull(cellValue)) attr.defaultValue else cellValue + attr.suffix)
        } else if (ColumnType.NUMERIC == attr.cellType) {
            if (com.ruoyi.common.utils.StringUtils.isNotNull(value)) {
                cell.setCellValue(
                    if (StringUtils.contains(toStr(value), ".")) toDouble(value).toString() else toInt(
                        value
                    ).toString()
                )
            }
        } else if (ColumnType.IMAGE == attr.cellType) {
            val anchor: ClientAnchor = XSSFClientAnchor(
                0,
                0,
                0,
                0,
                cell.columnIndex.toShort().toInt(),
                cell.row.rowNum,
                (cell.columnIndex + 1).toShort().toInt(),
                cell.row.rowNum + 1
            )
            val imagePath: String = toStr(value)!!
            if (isNotEmpty(imagePath)) {
                val data = ImageUtils.getImage(imagePath)
                getDrawingPatriarch(cell.sheet).createPicture(
                    anchor,
                    cell.sheet.workbook.addPicture(data, getImageType(data))
                )
            }
        }
    }

    /**
     * 获取图片类型,设置图片插入类型
     */
    fun getImageType(value: ByteArray?): Int {
        val type = FileTypeUtils.getFileExtendName(value)
        return when {
            "JPG".equals(type, ignoreCase = true) -> {
                Workbook.PICTURE_TYPE_JPEG
            }

            "PNG".equals(type, ignoreCase = true) -> {
                Workbook.PICTURE_TYPE_PNG
            }

            else -> Workbook.PICTURE_TYPE_JPEG
        }
    }

    /**
     * 创建表格样式
     */
    fun setDataValidation(attr: Excel, row: Row?, column: Int) {
        if (attr.name.indexOf("注：") >= 0) {
            sheet!!.setColumnWidth(column, 6000)
        } else {
            // 设置列宽
            sheet!!.setColumnWidth(column, ((attr.width + 0.72) * 256).toInt())
        }
        if (isNotEmpty(attr.prompt) || attr.combo.isNotEmpty()) {
            // 提示信息或只能选择不能输入的列内容.
            setPromptOrValidation(sheet, attr.combo, attr.prompt, 1, 100, column, column)
        }
    }

    /**
     * 添加单元格
     */
    fun addCell(attr: Excel, row: Row, vo: T, field: Field, column: Int): Cell? {
        var cell: Cell? = null
        try {
            // 设置行高
            row.height = maxHeight
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport) {
                // 创建cell
                cell = row.createCell(column)
                if (isSubListValue(vo) && getListCellValue(vo).size > 1 && attr.needMerge) {
                    val cellAddress = CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column)
                    sheet!!.addMergedRegion(cellAddress)
                }
                cell.cellStyle = styles!![com.ruoyi.common.utils.StringUtils.format(
                    "data_{}_{}_{}",
                    attr.align,
                    attr.color,
                    attr.backgroundColor
                )]

                // 用于读取对象中的属性
                val value = getTargetValue(vo, field, attr)
                val dateFormat: String = attr.dateFormat
                val readConverterExp: String = attr.readConverterExp
                val separator: String = attr.separator
                val dictType: String = attr.dictType
                if (isNotEmpty(dateFormat) && com.ruoyi.common.utils.StringUtils.isNotNull(
                        value
                    )
                ) {
                    cell.setCellValue(parseDateToStr(dateFormat, value))
                } else if (isNotEmpty(readConverterExp) && com.ruoyi.common.utils.StringUtils.isNotNull(
                        value
                    )
                ) {
                    cell.setCellValue(convertByExp(toStr(value)!!, readConverterExp, separator))
                } else if (isNotEmpty(dictType) && com.ruoyi.common.utils.StringUtils.isNotNull(
                        value
                    )
                ) {
                    cell.setCellValue(convertDictByExp(toStr(value)!!, dictType, separator))
                } else if (value is BigDecimal && -1 != attr.scale) {
                    cell.setCellValue(value.setScale(attr.scale, attr.roundingMode).toDouble())
                } else if (attr.handler != ExcelHandlerAdapter::class.java) {
                    cell.setCellValue(dataFormatHandlerAdapter(value, attr))
                } else {
                    // 设置列类型
                    setCellVo(value, attr, cell)
                }
                addStatisticsData(column, toStr(value)!!, attr)
            }
        } catch (e: Exception) {
            log.error("导出Excel失败{}", e)
        }
        return cell
    }

    /**
     * 设置 POI XSSFSheet 单元格提示或选择框
     *
     * @param sheet 表单
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    fun setPromptOrValidation(
        sheet: Sheet?, textlist: Array<String>, promptContent: String?, firstRow: Int, endRow: Int,
        firstCol: Int, endCol: Int,
    ) {
        val helper = sheet!!.dataValidationHelper
        val constraint =
            if (textlist.isNotEmpty()) helper.createExplicitListConstraint(textlist) else helper.createCustomConstraint(
                "DD1"
            )
        val regions = CellRangeAddressList(firstRow, endRow, firstCol, endCol)
        val dataValidation = helper.createValidation(constraint, regions)
        if (isNotEmpty(promptContent)) {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent)
            dataValidation.showPromptBox = true
        }
        // 处理Excel兼容性问题
        if (dataValidation is XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true)
            dataValidation.setShowErrorBox(true)
        } else {
            dataValidation.suppressDropDownArrow = false
        }
        sheet.addValidationData(dataValidation)
    }

    /**
     * 数据处理器
     *
     * @param value 数据值
     * @param excel 数据注解
     * @return
     */
    fun dataFormatHandlerAdapter(value: Any?, excel: Excel): String {
        var newValue: Any? = null
        try {
            val instance: Any = excel.handler::class.java.getDeclaredConstructor().newInstance()
            val formatMethod: Method = excel.handler::class.java.getMethod(
                "format",
                Any::class.java,
                Array<String>::class.java
            )
            newValue = formatMethod.invoke(instance, value, excel.args)
        } catch (e: java.lang.Exception) {
            log.error("不能格式化数据 " + excel.handler, e.message)
        }
        return toStr(newValue)!!
    }

    /**
     * 合计统计信息
     */
    private fun addStatisticsData(index: Int, text: String, entity: Excel?) {
        if (entity != null && entity.isStatistics) {
            var temp = 0.0
            if (!statistics.containsKey(index)) {
                statistics[index] = temp
            }
            try {
                temp = java.lang.Double.valueOf(text)
            } catch (e: NumberFormatException) {
            }
            statistics[index] = statistics[index]!! + temp
        }
    }

    /**
     * 创建统计行
     */
    fun addStatisticsRow() {
        if (statistics.isNotEmpty()) {
            val row = sheet!!.createRow(sheet!!.lastRowNum + 1)
            val keys: Set<Int> = statistics.keys
            var cell = row.createCell(0)
            cell.cellStyle = styles!!["total"]
            cell.setCellValue("合计")
            keys.forEach { key ->
                cell = row.createCell(key)
                cell.cellStyle = styles!!["total"]
                cell.setCellValue(DOUBLE_FORMAT.format(statistics[key]))
            }
            statistics.clear()
        }
    }

    /**
     * 编码文件名
     */
    fun encodingFilename(filename: String?): String {
        return UUID.randomUUID().toString() + "_" + filename + ".xlsx"
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    fun getAbsoluteFile(filename: String): String {
        val downloadPath: String = RuoYiConfig.getDownloadPath() + filename
        val desc = File(downloadPath)
        if (!desc.parentFile.exists()) {
            desc.parentFile.mkdirs()
        }
        return downloadPath
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo 实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws Exception
     */
    @Throws(Exception::class)
    private fun getTargetValue(vo: T, field: Field, excel: Excel): Any {
        var o = field[vo]
        if (isNotEmpty(excel.targetAttr)) {
            val target: String = excel.targetAttr
            if (target.contains(".")) {
                val targets = target.split("[.]".toRegex()).dropLastWhile { it.isEmpty() }.toTypedArray()
                targets.forEach { name ->
                    o = getValue(o, name)
                }
            } else {
                o = getValue(o, target)
            }
        }
        return o
    }

    /**
     * 以类的属性的get方法方法形式获取值
     *
     * @param o
     * @param name
     * @return value
     * @throws Exception
     */
    @Throws(Exception::class)
    private fun getValue(o: Any, name: String): Any {
        var newO = o
        if (com.ruoyi.common.utils.StringUtils.isNotNull(o) && isNotEmpty(name)) {
            val clazz: Class<*> = o.javaClass
            val field = clazz.getDeclaredField(name)
            field.isAccessible = true
            newO = field[o]
        }
        return newO
    }

    /**
     * 得到所有定义字段
     */
    private fun createExcelField() {
        fields = getFields()
        fields = fields!!.stream()
            .sorted(Comparator.comparing { objects: Array<Any> -> (objects[1] as Excel).sort })
            .collect(Collectors.toList())
        maxHeight = rowHeight
    }

    /**
     * 获取字段注解信息
     */
    fun getFields(): List<Array<Any>> {
        val fields: MutableList<Array<Any>> = ArrayList()
        val tempFields: MutableList<Field> = ArrayList()
        tempFields.addAll(listOf(*clazz.superclass.declaredFields))
        tempFields.addAll(listOf(*clazz.declaredFields))
        for (field in tempFields) {
            if (!ArrayUtils.contains(arrayOf(excludeFields), field.name)) {
                // 单注解
                if (field.isAnnotationPresent(Excel::class.java)) {
                    val attr = field.getAnnotation(Excel::class.java)
                    if (attr != null && (attr.type == Excel.Type.ALL || attr.type == type)) {
                        field.isAccessible = true
                        fields.add(arrayOf(field, attr))
                    }
                    if (MutableCollection::class.java.isAssignableFrom(field.type)) {
                        subMethod = getSubMethod(field.name, clazz)
                        val pt = field.genericType as ParameterizedType
                        val subClass = pt.actualTypeArguments[0] as Class<*>
                        subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel::class.java)
                    }
                }

                // 多注解
                if (field.isAnnotationPresent(Excels::class.java)) {
                    val attrs = field.getAnnotation(Excels::class.java)
                    val excels = attrs.value
                    for (attr in excels) {
                        if (attr.type == Excel.Type.ALL || attr.type == type) {
                            field.isAccessible = true
                            fields.add(arrayOf(field, attr))
                        }
                    }
                }
            }
        }
        return fields
    }

    /**
     * 根据注解获取最大行高
     */
    val rowHeight: Short
        get() {
            var maxHeight = 0.0
            for (os in fields!!) {
                val excel = os[1] as Excel
                maxHeight = Math.max(maxHeight, excel.height)
            }
            return (maxHeight * 20).toInt().toShort()
        }

    /**
     * 创建一个工作簿
     */
    fun createWorkbook() {
        wb = SXSSFWorkbook(500)
        sheet = wb!!.createSheet()
        wb!!.setSheetName(0, sheetName)
        styles = createStyles(wb!!)
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index 序号
     */
    fun createSheet(sheetNo: Int, index: Int) {
        // 设置工作表的名称.
        if (sheetNo > 1 && index > 0) {
            sheet = wb!!.createSheet()
            createTitle()
            wb!!.setSheetName(index, sheetName + index)
        }
    }

    /**
     * 获取单元格值
     *
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    fun getCellValue(row: Row?, column: Int): Any? {
        if (row == null) {
            return row
        }
        var `val`: Any = ""
        try {
            val cell = row.getCell(column)
            if (com.ruoyi.common.utils.StringUtils.isNotNull(cell)) {
                if (cell.cellType == CellType.NUMERIC || cell.cellType == CellType.FORMULA) {
                    `val` = cell.numericCellValue
                    `val` = if (DateUtil.isCellDateFormatted(cell)) {
                        DateUtil.getJavaDate(`val`) // POI Excel 日期格式转换
                    } else {
                        if (`val` % 1 != 0.0) {
                            BigDecimal(`val`.toString())
                        } else {
                            DecimalFormat("0").format(`val`)
                        }
                    }
                } else if (cell.cellType == CellType.STRING) {
                    `val` = cell.stringCellValue
                } else if (cell.cellType == CellType.BOOLEAN) {
                    `val` = cell.booleanCellValue
                } else if (cell.cellType == CellType.ERROR) {
                    `val` = cell.errorCellValue
                }
            }
        } catch (e: Exception) {
            return `val`
        }
        return `val`
    }

    /**
     * 判断是否是空行
     *
     * @param row 判断的行
     * @return
     */
    private fun isRowEmpty(row: Row?): Boolean {
        if (row == null) {
            return true
        }
        for (i in row.firstCellNum until row.lastCellNum) {
            val cell = row.getCell(i)
            if (cell != null && cell.cellType != CellType.BLANK) {
                return false
            }
        }
        return true
    }

    /**
     * 格式化不同类型的日期对象
     *
     * @param dateFormat 日期格式
     * @param val 被格式化的日期对象
     * @return 格式化后的日期字符
     */
    fun parseDateToStr(dateFormat: String, `val`: Any?): String {
        if (`val` == null) {
            return ""
        }
        val str: String?
        str = if (`val` is Date) {
            DateUtils.parseDateToStr(dateFormat, `val` as Date?)
        } else if (`val` is LocalDateTime) {
            DateUtils.parseDateToStr(
                dateFormat,
                DateUtils.toDate(`val`)
            )
        } else if (`val` is LocalDate) {
            DateUtils.parseDateToStr(
                dateFormat,
                DateUtils.toDate(`val` as LocalDate?)
            )
        } else {
            `val`.toString()
        }
        return str
    }

    /**
     * 是否有对象的子列表
     */
    val isSubList: Boolean
        get() = com.ruoyi.common.utils.StringUtils.isNotNull(subFields) && subFields!!.isNotEmpty()

    /**
     * 是否有对象的子列表，集合不为空
     */
    fun isSubListValue(vo: T): Boolean {
        return com.ruoyi.common.utils.StringUtils.isNotNull(subFields) && subFields!!.isNotEmpty() && com.ruoyi.common.utils.StringUtils.isNotNull(
            getListCellValue(vo)
        ) && getListCellValue(vo).isNotEmpty()
    }

    /**
     * 获取集合的值
     */
    fun getListCellValue(obj: Any?): Collection<*> {
        val value: Any = try {
            subMethod!!.invoke(obj, *arrayOf())
        } catch (e: Exception) {
            return ArrayList<Any>()
        }
        return value as Collection<*>
    }

    /**
     * 获取对象的子列表方法
     *
     * @param name 名称
     * @param pojoClass 类对象
     * @return 子列表方法
     */
    fun getSubMethod(name: String, pojoClass: Class<*>): Method? {
        val getMethodName = StringBuffer("get")
        getMethodName.append(name.substring(0, 1).uppercase(Locale.getDefault()))
        getMethodName.append(name.substring(1))
        var method: Method? = null
        try {
            method = pojoClass.getMethod(getMethodName.toString(), *arrayOf())
        } catch (e: Exception) {
            log.error("获取对象异常{}", e.message)
        }
        return method
    }

    companion object {
        private val log = LoggerFactory.getLogger(ExcelUtil::class.java)
        const val FORMULA_REGEX_STR = "=|-|\\+|@"
        val FORMULA_STR = arrayOf("=", "-", "+", "@")

        /**
         * Excel sheet最大行数，默认65536
         */
        const val sheetSize = 65536

        /**
         * 数字格式
         */
        private val DOUBLE_FORMAT = DecimalFormat("######0.00")

        /**
         * 获取画布
         */
        fun getDrawingPatriarch(sheet: Sheet): Drawing<*> {
            if (sheet.drawingPatriarch == null) {
                sheet.createDrawingPatriarch()
            }
            return sheet.drawingPatriarch
        }

        /**
         * 解析导出值 0=男,1=女,2=未知
         *
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @param separator 分隔符
         * @return 解析后值
         */
        fun convertByExp(propertyValue: String, converterExp: String, separator: String): String {
            val propertyString = StringBuilder()
            val convertSource = converterExp.split(",".toRegex()).dropLastWhile { it.isEmpty() }.toTypedArray()
            for (item in convertSource) {
                val itemArray = item.split("=".toRegex()).dropLastWhile { it.isEmpty() }.toTypedArray()
                if (StringUtils.containsAny(propertyValue, separator)) {
                    for (value in propertyValue.split(separator.toRegex()).dropLastWhile { it.isEmpty() }
                        .toTypedArray()) {
                        if (itemArray[0] == value) {
                            propertyString.append(itemArray[1] + separator)
                            break
                        }
                    }
                } else {
                    if (itemArray[0] == propertyValue) {
                        return itemArray[1]
                    }
                }
            }
            return StringUtils.stripEnd(propertyString.toString(), separator)
        }

        /**
         * 反向解析值 男=0,女=1,未知=2
         *
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @param separator 分隔符
         * @return 解析后值
         */
        fun reverseByExp(propertyValue: String, converterExp: String, separator: String): String {
            val propertyString = StringBuilder()
            val convertSource = converterExp.split(",".toRegex()).dropLastWhile { it.isEmpty() }.toTypedArray()
            for (item in convertSource) {
                val itemArray = item.split("=".toRegex()).dropLastWhile { it.isEmpty() }.toTypedArray()
                if (StringUtils.containsAny(propertyValue, separator)) {
                    for (value in propertyValue.split(separator.toRegex()).dropLastWhile { it.isEmpty() }
                        .toTypedArray()) {
                        if (itemArray[1] == value) {
                            propertyString.append(itemArray[0] + separator)
                            break
                        }
                    }
                } else {
                    if (itemArray[1] == propertyValue) {
                        return itemArray[0]
                    }
                }
            }
            return StringUtils.stripEnd(propertyString.toString(), separator)
        }

        /**
         * 解析字典值
         *
         * @param dictValue 字典值
         * @param dictType 字典类型
         * @param separator 分隔符
         * @return 字典标签
         */
        fun convertDictByExp(dictValue: String, dictType: String, separator: String): String {
            return DictUtils.getDictLabel(dictType, dictValue, separator)
        }

        /**
         * 反向解析值字典值
         *
         * @param dictLabel 字典标签
         * @param dictType 字典类型
         * @param separator 分隔符
         * @return 字典值
         */
        fun reverseDictByExp(dictLabel: String, dictType: String, separator: String): String {
            return DictUtils.getDictValue(dictType, dictLabel, separator)
        }

        /**
         * 获取Excel2003图片
         *
         * @param sheet 当前sheet对象
         * @param workbook 工作簿对象
         * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
         */
        fun getSheetPictures03(sheet: HSSFSheet, workbook: HSSFWorkbook?): Map<String, PictureData> {
            val sheetIndexPicMap: MutableMap<String, PictureData> = HashMap()
            val pictures = workbook!!.allPictures
            return if (pictures.isNotEmpty()) {
                for (shape in sheet.drawingPatriarch.children) {
                    val anchor = shape.anchor as HSSFClientAnchor
                    if (shape is HSSFPicture) {
                        val pictureIndex = shape.pictureIndex - 1
                        val picData = pictures[pictureIndex]
                        val picIndex = anchor.row1.toString() + "_" + anchor.col1.toString()
                        sheetIndexPicMap[picIndex] = picData
                    }
                }
                sheetIndexPicMap
            } else {
                sheetIndexPicMap
            }
        }

        /**
         * 获取Excel2007图片
         *
         * @param sheet 当前sheet对象
         * @param workbook 工作簿对象
         * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
         */
        fun getSheetPictures07(sheet: XSSFSheet, workbook: XSSFWorkbook?): Map<String, PictureData> {
            val sheetIndexPicMap: MutableMap<String, PictureData> = HashMap()
            for (dr in sheet.relations) {
                if (dr is XSSFDrawing) {
                    val shapes = dr.shapes
                    for (shape in shapes) {
                        if (shape is XSSFPicture) {
                            val anchor = shape.preferredSize
                            val ctMarker = anchor.from
                            val picIndex = ctMarker.row.toString() + "_" + ctMarker.col
                            sheetIndexPicMap[picIndex] = shape.pictureData
                        }
                    }
                }
            }
            return sheetIndexPicMap
        }
    }
}
